package com.youan.log.modules.audit.dataanalysis;

import com.youan.log.common.threadtask.ThreadTaskProgressor;
import com.youan.log.modules.audit.entity.DbAssetSearch;
import com.youan.log.modules.audit.service.IDbAssetSearchService;
import org.springblade.core.tool.utils.SpringUtil;
import org.springframework.jdbc.core.JdbcTemplate;

import javax.sql.DataSource;
import java.util.ArrayList;
import java.util.List;

public class DbAssetSearchDataExtract {

    private String date;
    private ThreadTaskProgressor taskProgressor;
    private IDbAssetSearchService dbAssetSearchService;
    private JdbcTemplate jdbcTemplate;
    private IP2Region ip2Region;
    private List<DbAssetSearch> dbAssetSearchList = new ArrayList<>();
    int BATCH_SIZE = 5000;

    public DbAssetSearchDataExtract(String date, ThreadTaskProgressor taskProgressor, IP2Region ip2Region) {
        this.date = date;
        this.taskProgressor = taskProgressor;
        this.ip2Region = ip2Region;
        DataSource dataSource = SpringUtil.getBean(DataSource.class);
        this.jdbcTemplate = new JdbcTemplate(dataSource);
        dbAssetSearchService = SpringUtil.getBean(IDbAssetSearchService.class);

        taskProgressor.updateProgressTotal(taskProgressor.getProgressTotalNum() + getCount());

    }

    public void extract() {
        String sql = "SELECT DISTINCT\n" +
                " dest_ip ip,\n" +
                " dest_country ip_country,\n" +
                " dest_province ip_province,\n" +
                " dest_city ip_city\n" +
                "FROM\n" +
                " log_statistical\n" +
                "WHERE\n" +
                " issue_type IN (\n" +
                "  '10050001485',\n" +
                "  '10050001949',\n" +
                "  '10050001950',\n" +
                "  '10050001994',\n" +
                "  '10050001999'\n" +
                " )\n" +
                "AND visit_date = '" + this.date + "'\n" +
                "AND NOT EXISTS (SELECT * FROM log_db_asset_search a WHERE a.ip = dest_ip)";

        buildAndSaveEntity(sql);

        sql = "SELECT DISTINCT\n" +
                " source_ip ip,\n" +
                " source_country ip_country,\n" +
                " source_province ip_province,\n" +
                " source_city ip_city\n" +
                "FROM\n" +
                " log_statistical\n" +
                "WHERE\n" +
                " issue_type IN (\n" +
                "  '10050001084',\n" +
                "  '10050001476'\n" +
                " )\n" +
                "AND visit_date = '" + this.date + "'\n" +
                "AND NOT EXISTS (SELECT * FROM log_db_asset_search a WHERE a.ip = source_ip)";

        buildAndSaveEntity(sql);

        // 这里不再循环去原始表中取数据了，速度太慢
        // 1.先将ip数据写入到log_db_asset_search表中 插入除最早发现日期外的其他字段
        // 2. 直接update log_db_asset_search 关联log_statistics表得到该数据最早的time
        String updateSql = "UPDATE log_db_asset_search search\n" +
                "SET search.earliest_start_time = IFNULL(\n" +
                " (\n" +
                "  SELECT\n" +
                "   min(a.first_occurrence_time)\n" +
                "  FROM\n" +
                "   log_statistical a\n" +
                "  WHERE\n" +
                "   search.ip = a.source_ip\n" +
                "  AND a.visit_date = '" + this.date + "'\n" +
                "  GROUP BY\n" +
                "   a.source_ip\n" +
                " ),\n" +
                " '2999-10-10'\n" +
                ")\n" +
                "WHERE\n" +
                " search.earliest_start_time IS NULL;";
        jdbcTemplate.execute(updateSql);
        updateSql = "UPDATE log_db_asset_search search\n" +
                "SET search.earliest_start_time = LEAST(\n" +
                " earliest_start_time,\n" +
                " IFNULL((\n" +
                "  SELECT\n" +
                "   min(a.first_occurrence_time)\n" +
                "  FROM\n" +
                "   log_statistical a\n" +
                "  WHERE\n" +
                "   search.ip = a.dest_ip\n" +
                "  AND a.visit_date = '" + this.date + "'\n" +
                "  GROUP BY\n" +
                "   a.dest_ip\n" +
                " ), '2999-10-10')\n" +
                ")\n" +
                "WHERE\n" +
                " search.earliest_start_time IS NULL\n" +
                "OR SUBSTRING(\n" +
                " search.earliest_start_time,\n" +
                " 1,\n" +
                " 10\n" +
                ") >= '" + this.date + "'\n";
        jdbcTemplate.execute(updateSql);
    }

    private void buildAndSaveEntity(String sql) {

        dbAssetSearchList = new ArrayList<>();

        jdbcTemplate.query(sql, rs -> {
            DbAssetSearch item = new DbAssetSearch();

            item.setIp(rs.getString("ip"));
            item.setIpCountry(rs.getString("ip_country"));
            item.setIpProvince(rs.getString("ip_province"));
            item.setIpCity(rs.getString("ip_city"));

            // 根据ip得到运营商
            String ip = item.getIp();
            IP2Region.Region region = ip2Region.ip2Region(ip);
            if(region != null) {
                item.setIpIsp(region.getIsp());
            }

            dbAssetSearchList.add(item);
            if(dbAssetSearchList.size() >= BATCH_SIZE) {
                dbAssetSearchService.saveBatch(dbAssetSearchList);
                taskProgressor.addProgressNum(dbAssetSearchList.size());
                dbAssetSearchList = new ArrayList<>();
            }
        });


        dbAssetSearchService.saveBatch(dbAssetSearchList);
        taskProgressor.addProgressNum(dbAssetSearchList.size());
    }

    private int getCount() {
        String sql = "SELECT\n" +
                " count(1)\n" +
                "FROM\n" +
                " (\n" +
                "  SELECT DISTINCT\n" +
                "   dest_ip ip,\n" +
                "   dest_country ip_country,\n" +
                "   dest_province ip_province,\n" +
                "   dest_city ip_city\n" +
                "  FROM\n" +
                "   log_statistical\n" +
                "  WHERE\n" +
                "   issue_type IN (\n" +
                "    '10050001485',\n" +
                "    '10050001949',\n" +
                "    '10050001950',\n" +
                "    '10050001994',\n" +
                "    '10050001999'\n" +
                "   )\n" +
                "   and visit_date = '" + this.date + "'\n" +
                "  UNION\n" +
                "   SELECT DISTINCT\n" +
                "    source_ip,\n" +
                "    source_country,\n" +
                "    source_province,\n" +
                "    source_city\n" +
                "   FROM\n" +
                "    log_statistical\n" +
                "   WHERE\n" +
                "    issue_type IN (\n" +
                "     '10050001084',\n" +
                "     '10050001476'\n" +
                "    )\n" +
                "    and visit_date = '" + this.date + "'\n" +
                " ) a\n" +
                "WHERE\n" +
                " ip NOT IN (\n" +
                "  SELECT\n" +
                "   ip\n" +
                "  FROM\n" +
                "   log_db_asset_search\n" +
                " )";
        return jdbcTemplate.queryForObject(sql, Integer.class);
    };

}
